package com.lxf.test.androidtest.abc;

import android.content.ContentValues;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.net.Uri;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.Button;
import android.widget.ImageButton;
import android.widget.TextView;
import android.widget.Toast;

import com.lxf.test.androidtest.R;

import java.io.DataInputStream;
import java.io.FileInputStream;

public class MainActivity extends AppCompatActivity {
    private Button createBtn;
    private Button insertBtn;
    private Button queryBtn;
    private Button fileBtn;
    private Button querylocalBtn;
    private Button delBtn;
    private ImageButton mapBtn;
    private TextView res;
    private Button btn_addtable;
    /**
     * ATTENTION: This was auto-generated to implement the App Indexing API.
     * See https://g.co/AppIndexing/AndroidStudio for more information.
     */

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        creatView();
        setListener();
        // ATTENTION: This was auto-generated to implement the App Indexing API.
        // See https://g.co/AppIndexing/AndroidStudio for more information.
    }


    //通过findViewById获得Button对象的方法
    private void creatView() {
        createBtn = (Button) findViewById(R.id.createDatabase);
        insertBtn = (Button) findViewById(R.id.insert);
        queryBtn = (Button) findViewById(R.id.query);
        fileBtn = (Button)findViewById(R.id.file);
        querylocalBtn = (Button) findViewById(R.id.querylocal);
        mapBtn = (ImageButton) findViewById(R.id.imageButton);
        res = (TextView)findViewById(R.id.res);
        delBtn = (Button)findViewById(R.id.del);
        btn_addtable = (Button) findViewById(R.id.add_table);
    }

    //为按钮注册监听的方法
    private void setListener() {
        createBtn.setOnClickListener(new CreateListener());
        insertBtn.setOnClickListener(new InsertListener());
        queryBtn.setOnClickListener(new QueryListener());
        try {
            querylocalBtn.setOnClickListener(new QueryNewListener());
        }catch (Exception e){
            e.printStackTrace();
        }
        mapBtn.setOnClickListener(new ToMapListener());
        delBtn.setOnClickListener(new DelListener());
        fileBtn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Intent intent = new Intent(Intent.ACTION_GET_CONTENT);
                intent.setType("*/*");//设置类型，我这里是任意类型，任意后缀的可以这样写。
                intent.addCategory(Intent.CATEGORY_OPENABLE);
                startActivityForResult(intent, 1);
                //startActivityForResult(Intent.createChooser(intent, "请选择一个要上传的文件"),FILE_SELECT_CODE);
            }
        });

        //测试添加表
        btn_addtable.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                MyDBHelper dbHelper = new MyDBHelper(MainActivity.this, "stu_db", null, 1);
                SQLiteDatabase db = dbHelper.getWritableDatabase();

                String sql = "select count(*) from sqlite_master where type='table' and tbl_name='TASKFINISH'";
                //"SELECT tbl_name FROM sqlite_master WHERE type = 'table';"
                String tbnames = "";
                //Cursor cursor = db.query("sqlite_master", new String[]{"tbl_name" }, "type=?", new String[]{"table"}, null, null, null);
                Cursor cursor = db.rawQuery(sql, null);
                if(cursor.moveToNext()){
                    int i = cursor.getInt(0);
                    if(i==1){
                        show("表TASKFINISH存在,先删除");
                        //删除表
                        sql = "drop table TASKFINISH";
                        db.execSQL(sql);
                        show("删除表TASKFINISH成功!");
                        cursor = db.query("sqlite_master", new String[]{"tbl_name" }, "type=?", new String[]{"table"}, null, null, null);
                        while(cursor.moveToNext()){
                            String tbname = cursor.getString(cursor.getColumnIndex("tbl_name"));
                            tbnames +=tbname+"-";
                        }
                        show(tbnames);
                        //创建表
                        sql = "create table TASKFINISH(" +
                                "exployeeid text primary key," +
                                "taskuid text," +
                                "workdate text)";
                        db.execSQL(sql);
                        show("表TASKFINISH成功再次创建!");
                    }
                }
                tbnames = "";
                cursor = db.query("sqlite_master", new String[]{"tbl_name" }, "type=?", new String[]{"table"}, null, null, null);
                while(cursor.moveToNext()){
                    String tbname = cursor.getString(cursor.getColumnIndex("tbl_name"));
                    tbnames +=tbname+"-";
                }
                show(tbnames);
            }
        });
    }

    //选择文件后的回调函数
    protected void onActivityResult(int requestCode, int resultCode, Intent data) {
        if (resultCode == RESULT_OK) {//是否选择，没选择就不会继续
            Uri uri = data.getData();//得到uri，后面就是将uri转化成file的过程。
            String path = uri.getPath();
            System.out.print("test getpath:" + path);
            addData(path);
            //Toast.makeText(MainActivity.this, path, Toast.LENGTH_SHORT).show();

        }
    }


    public void addData(String path) {
        MyDBHelper dbHelper = new MyDBHelper(MainActivity.this, "stu_db", null, 1);
        //得到一个可写的数据库
        SQLiteDatabase db = dbHelper.getWritableDatabase();

//        String sql = "create table ARFILE_GROUP( ID VARCHAR(32) not null,GROUPNAME VARCHAR(60),SHORTNAME VARCHAR(16))";
//        db.execSQL(sql);
//        System.out.println("testsql: 创建表ARFILE_GROUP");
        try{
            FileInputStream fis = new FileInputStream(path);
            StringBuffer sBuffer = new StringBuffer();
            DataInputStream dataIO = new DataInputStream(fis);
            String temp = "";
            String show = null;
            String strLine = "";
            String [] ss = null;
            while((strLine =  dataIO.readLine()) != null) {
                //Toast.makeText(MainActivity.this, strLine, Toast.LENGTH_SHORT).show();
                //temp = convertCodeAndGetText(strLine);
                //Toast.makeText(MainActivity.this,temp, Toast.LENGTH_SHORT).show();
                ss = strLine.split(",");
                //生成ContentValues对象 //key:列名，value:想插入的值
                ContentValues cv = new ContentValues();
                //往ContentValues对象存放数据，键-值对模式
                cv.put("ID", ss[0]);
                cv.put("GROUPNAME", ss[1]);
                cv.put("SHORTNAME", ss[2]);
                //调用insert方法，将数据插入数据库
                db.insert("ARFILE_GROUP", null, cv);
                //show +="("+ss[0]+","+ss[1]+","+ss[2]+");";
            }
            res.setText(show);
            //关闭数据库
            db.close();
            dataIO.close();
            fis.close();
        }catch (Exception e){
            Toast.makeText(MainActivity.this, "出错了!", Toast.LENGTH_SHORT).show();
            e.printStackTrace();
        }

    }


    //创建数据库的方法
    class CreateListener implements View.OnClickListener {

        @Override
        public void onClick(View v) {
            //创建StuDBHelper对象
            MyDBHelper dbHelper = new MyDBHelper(MainActivity.this, "test_db", null, 1);
            //得到一个可读的SQLiteDatabase对象
            SQLiteDatabase db = dbHelper.getReadableDatabase();
        }
    }


    //插入数据的方法
    class InsertListener implements View.OnClickListener {
        @Override
        public void onClick(View v) {

            MyDBHelper dbHelper = new MyDBHelper(MainActivity.this, "stu_db", null, 1);
            //得到一个可写的数据库
            SQLiteDatabase db = dbHelper.getWritableDatabase();

            //生成ContentValues对象 //key:列名，value:想插入的值
            ContentValues cv = new ContentValues();
            //往ContentValues对象存放数据，键-值对模式
            cv.put("id", 1);
            cv.put("sname", "xiaoming");
            cv.put("sage", 21);
            cv.put("ssex", "male");
            //调用insert方法，将数据插入数据库
            db.insert("stu_table", null, cv);
            //关闭数据库
            db.close();
        }
    }


    //查询数据的方法
    class QueryListener implements View.OnClickListener {

        @Override
        public void onClick(View v) {

            MyDBHelper dbHelper = new MyDBHelper(MainActivity.this, "stu_db", null, 1);
//得到一个可写的数据库
            SQLiteDatabase db = dbHelper.getReadableDatabase();
//参数1：表名
//参数2：要想显示的列
//参数3：where子句
//参数4：where子句对应的条件值
//参数5：分组方式
//参数6：having条件
//参数7：排序方式
            Cursor cursor = db.query("stu_table", new String[]{"id", "sname", "sage", "ssex"}, "id=?", new String[]{"1"}, null, null, null);
            while (cursor.moveToNext()) {
                String name = cursor.getString(cursor.getColumnIndex("sname"));
                String age = cursor.getString(cursor.getColumnIndex("sage"));
                String sex = cursor.getString(cursor.getColumnIndex("ssex"));
                System.out.println("query------->" + "姓名：" + name + " " + "年龄：" + age + " " + "性别：" + sex);
                //Toast.makeText(getApplicationContext(), "query------->" + "姓名：" + name + " " + "年龄：" + age + " " + "性别：" + sex, Toast.LENGTH_SHORT).show();
            }
//关闭数据库
            db.close();
        }
    }


    //创建数据库的方法
    class QlocalListener implements View.OnClickListener {


        @Override
        public void onClick(View v) {
            //创建StuDBHelper对象
            MyDBHelper dbHelper = new MyDBHelper(MainActivity.this, "test_db", null, 1);
            //得到一个可读的SQLiteDatabase对象
            SQLiteDatabase db = dbHelper.getReadableDatabase();
        }
    }

    //查询读取文件数据的方法
    class QueryNewListener implements View.OnClickListener {

        @Override
        public void onClick(View v) {
            String tbnames = "";

            MyDBHelper dbHelper = new MyDBHelper(MainActivity.this, "stu_db", null, 1);
            //得到一个可写的数据库
            SQLiteDatabase db = dbHelper.getReadableDatabase();
            //"SELECT tbl_name FROM sqlite_master WHERE type = 'table';"
//            Cursor cursor = db.query("sqlite_master", new String[]{"tbl_name" }, "type=?", new String[]{"table"}, null, null, null);
//            while(cursor.moveToNext()){
//                String tbname = cursor.getString(cursor.getColumnIndex("tbl_name"));
//                tbnames += tbname+";";
//            }
            System.out.println("testsql: 开始查询ARFILE_GROUP");
            Cursor cursor = db.query("ARFILE_GROUP", new String[]{"ID", "GROUPNAME", "SHORTNAME", }, null, null, null, null, null);
            while (cursor.moveToNext()) {
                String ID = cursor.getString(cursor.getColumnIndex("ID"));
                String GROUPNAME = cursor.getString(cursor.getColumnIndex("GROUPNAME"));
                String SHORTNAME = cursor.getString(cursor.getColumnIndex("SHORTNAME"));
                //System.out.println("query------->" + "姓名：" + name + " " + "年龄：" + age + " " + "性别：" + sex);
                //Toast.makeText(getApplicationContext(), "query------->" + "姓名：" + name + " " + "年龄：" + age + " " + "性别：" + sex, Toast.LENGTH_SHORT).show();
                tbnames += "("+ID+","+GROUPNAME+","+SHORTNAME+");";
                System.out.println("testsql: 查询ARFILE_GROUP中----"+tbnames);
            }
            System.out.println("testsql: 已查询ARFILE_GROUP");
            res.setText(tbnames);
//关闭数据库
            db.close();
        }
    }

    class ToMapListener implements View.OnClickListener {
        @Override
        public void onClick(View v) {
            Intent intent = new Intent();
            intent.setClass(MainActivity.this, MapActivity.class);
            startActivity(intent);
        }
    }


    //创建数据库的方法
    class DelListener implements View.OnClickListener {


        @Override
        public void onClick(View v) {
            //创建StuDBHelper对象
            MyDBHelper dbHelper = new MyDBHelper(MainActivity.this, "stu_db", null, 1);
            //得到一个可读的SQLiteDatabase对象
            SQLiteDatabase db = dbHelper.getReadableDatabase();
            String sql ="delete from ARFILE_GROUP";
            db.execSQL(sql);
            db.close();
        }
    }


    public void show(String text){
        Toast.makeText(this, text, Toast.LENGTH_SHORT).show();
    }
}
